﻿/* *********************************
 * 项目名称：
 * 模块功能：
 * ---------------------------------
 * 创 建 者：
 * 创建时间：20130730
 * ----------------------------------
 * 修 改 者：
 * 修改日期：
 * *********************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Reflection;
namespace Data
{
    public class DataAccess
    {
        #region 更新
        /// <summary>
        /// 插入一条记录
        /// </summary>
        /// <param name="t">要插入的对象</param>
        /// <param name="primaryKey">主键</param>
        /// <returns></returns>
        public long Add<T>(T t, string primaryKey) where T : new()
        {
            Type type = typeof(T);
            PropertyInfo[] propertys = type.GetProperties();
            SqlParameter[] p = new SqlParameter[propertys.Length - 1];
            string fields = null;
            string values = null;
            int i = 0;
            foreach (PropertyInfo pi in propertys)
            {
                if (pi.Name.ToLower() != primaryKey.ToLower())
                {
                    fields += "[" + pi.Name + "],";
                    values += "@" + pi.Name + ",";
                    if (pi.GetValue(t, null) == null || pi.GetValue(t, null).ToString() == new DateTime().ToString())
                    {
                        //p[i] = new SqlParameter("@" + pi.Name, DBNull.Value);
                        if (pi.PropertyType.FullName != null && "system.byte[]".Equals(pi.PropertyType.FullName.ToLower()))
                        {
                            p[i] = new SqlParameter("@" + pi.Name, new System.Byte[] { (System.Byte)0 });
                        }
                        else if (pi.PropertyType.Name != null && "byte[]".Equals(pi.PropertyType.Name.ToLower()))
                        {
                            p[i] = new SqlParameter("@" + pi.Name, new System.Byte[] { (System.Byte)0 });
                        }
                        else
                        {
                            p[i] = new SqlParameter("@" + pi.Name, DBNull.Value);
                        }
                    }
                    else
                    {
                        if (pi.GetValue(t, null).GetType().Name == typeof(System.DateTime).Name)
                        {
                            p[i] = new SqlParameter("@" + pi.Name, pi.GetValue(t, null).ToString());
                        }
                        else
                        {
                            p[i] = new SqlParameter("@" + pi.Name, pi.GetValue(t, null));
                        }
                    }
                    i++;
                }
            }
            fields = fields.TrimEnd(',');
            values = values.TrimEnd(',');
            string sql = "insert into [" + type.Name + "](" + fields + ")values(" + values + ");select @@identity;";
            return long.Parse(new DbHelp().GetScalar(DBConfig.ConnectionSettings, CommandType.Text, sql, p).ToString());
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public long AddNoPK<T>(T t) where T : new()
        {
            Type type = typeof(T);
            PropertyInfo[] propertys = type.GetProperties();
            SqlParameter[] p = new SqlParameter[propertys.Length];
            string fields = null;
            string values = null;
            int i = 0;
            foreach (PropertyInfo pi in propertys)
            {                
                    fields += "[" + pi.Name + "],";
                    values += "@" + pi.Name + ",";
                    if (pi.GetValue(t, null) == null || pi.GetValue(t, null).ToString() == new DateTime().ToString())
                    {
                        if (pi.PropertyType.FullName != null && "system.byte[]".Equals(pi.PropertyType.FullName.ToLower()))
                        {
                            p[i] = new SqlParameter("@" + pi.Name, new System.Byte[] { (System.Byte)0 });
                        }
                        else if (pi.PropertyType.Name != null && "byte[]".Equals(pi.PropertyType.Name.ToLower()))
                        {
                            p[i] = new SqlParameter("@" + pi.Name, new System.Byte[] { (System.Byte)0 });
                        }                            
                        else
                        {
                            p[i] = new SqlParameter("@" + pi.Name, DBNull.Value);
                        }
                    }
                    else
                    {
                        if (pi.GetValue(t, null).GetType().Name == typeof(System.DateTime).Name)
                        {
                            p[i] = new SqlParameter("@" + pi.Name, pi.GetValue(t, null).ToString());
                        }
                        else
                        {
                            p[i] = new SqlParameter("@" + pi.Name, pi.GetValue(t, null));
                        }
                    }
                    i++;               
            }
            fields = fields.TrimEnd(',');
            values = values.TrimEnd(',');
            string sql = "insert into [" + type.Name + "](" + fields + ")values(" + values + ");";
            return (new DbHelp().Save(DBConfig.ConnectionSettings, CommandType.Text, sql, p));
        }
        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <param name="primaryKey">主键</param>
        /// <param name="id">主键的值</param>
        /// <returns></returns>
        public bool Delete<T>(long id, string primaryKey) where T : new()
        {
            T t = new T();
            Type type = typeof(T);
            string sql = "delete from [" + type.Name + "] where [" + primaryKey + "]=@id";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@id", id);
            if (new DbHelp().Save(DBConfig.ConnectionSettings, CommandType.Text, sql, p) > 0)
            { return true; }
            else
            { return false; }
        }
        /// <summary>
        /// 删除记录
        /// </summary>
        /// <param name="primaryKey">主键</param>
        /// <param name="id">主键的值</param>
        /// <returns></returns>
        public int Drop<T>(string ids, string primaryKey) where T : new()
        {
            T t = new T();
            string sql = "delete from [" + typeof(T).Name + "] where [" + primaryKey + "] in(" + ids + ")";
            return new DbHelp().Save(DBConfig.ConnectionSettings, CommandType.Text, sql, null);
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="sql">参数化的SQL语句</param>
        /// <param name="SqlParameter">参数</param>
        /// <returns></returns>
        public int Delete(string sql, params DbParameter[] SqlParameter)
        {
            return new DbHelp().Save(DBConfig.ConnectionSettings, CommandType.Text, sql, SqlParameter);
        }
        /// <summary>
        /// 更新一条记录
        /// </summary>
        /// <param name="t">要更新的对象</param>
        /// <param name="primaryKey">主键</param>
        /// <returns></returns>
        public int Update<T>(T t, string primaryKey) where T : new()
        {
            Type type = typeof(T);
            string sql = "update [" + type.Name + "] set ";
            PropertyInfo[] propertys = type.GetProperties();
            PropertyInfo primaryKeyProperty = null;
            SqlParameter[] p = new SqlParameter[propertys.Length];
            int i = 0;
            foreach (PropertyInfo pi in propertys)
            {
                if (pi.Name.ToLower() != primaryKey.ToLower())
                {
                    sql += "[" + pi.Name + "]=@" + pi.Name + ",";

                    if (pi.GetValue(t, null) == null || pi.GetValue(t, null).ToString() == new DateTime().ToString())
                    {
                        p[i] = new SqlParameter("@" + pi.Name, DBNull.Value);
                    }
                    else
                    {
                        if (pi.GetValue(t, null).GetType().Name == typeof(System.DateTime).Name)
                        {
                            p[i] = new SqlParameter("@" + pi.Name, pi.GetValue(t, null).ToString());
                        }
                        else
                        {
                            p[i] = new SqlParameter("@" + pi.Name, pi.GetValue(t, null));
                        }
                    }
                    i++;
                }
                else
                {
                    primaryKeyProperty = pi;
                }
            }
            if (primaryKeyProperty.GetValue(t, null) == null || primaryKeyProperty.GetValue(t, null).ToString() == new DateTime().ToString())
            {
                p[propertys.Length - 1] = new SqlParameter("@" + primaryKeyProperty.Name, DBNull.Value);
            }
            else
            {
                if (primaryKeyProperty.GetValue(t, null).GetType().Name == typeof(System.DateTime).Name)
                {
                    p[propertys.Length - 1] = new SqlParameter("@" + primaryKeyProperty.Name, primaryKeyProperty.GetValue(t, null).ToString());
                }
                else
                {
                    p[propertys.Length - 1] = new SqlParameter("@" + primaryKeyProperty.Name, primaryKeyProperty.GetValue(t, null));
                }
            }
            sql = sql.TrimEnd(',');
            sql += " where ";
            sql += "[" + primaryKey + "]=@" + primaryKey;
            return new DbHelp().Save(DBConfig.ConnectionSettings, CommandType.Text, sql, p);
        }

        /// <summary>
        /// 批量更新
        /// </summary>
        /// <param name="sql">参数化的SQL语句</param>
        /// <param name="SqlParameter">参数</param>
        /// <returns></returns>
        public int Update(string sql, params DbParameter[] SqlParameter)
        {
            return new DbHelp().Save(DBConfig.ConnectionSettings, CommandType.Text, sql, SqlParameter);
        }
        #endregion

        #region 抽取单条记录
        /// <summary>
        /// 抽出一条记录
        /// </summary>
        /// <param name="primaryKey">主键</param>
        /// <param name="id">主键的值</param>
        /// <returns></returns>
        public T Get<T>(long id, string primaryKey) where T : new()
        {
            T t = new T();
            Type type = typeof(T);
            string sql = "select top 1 " + ConvertHelper<T>.GetCollectionsColumnName() + " from [" + type.Name + "] where [" + primaryKey + "]=@id";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@id", id);
            DataSet ds = new DbHelp().ReadDataSet(DBConfig.ConnectionSettings, CommandType.Text, sql, p);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ConvertHelper<T>.ConvertToModel(ds.Tables[0]);
            }
            else
            {
                return default(T);
            }
        }
        /// <summary>
        /// 抽出一条记录
        /// </summary>
        /// <param name="ColVar">值</param>
        /// <param name="primaryKey">检索列名</param>
        /// <returns></returns>
        public T Get_SetStringVarCol<T>(string ColVar, string primaryKey) where T : new()
        {
            T t = new T();
            Type type = typeof(T);
            string sql = "select top 1 " + ConvertHelper<T>.GetCollectionsColumnName() + " from [" + type.Name + "] where [" + primaryKey + "]=@id";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@id", ColVar);
            DataSet ds = new DbHelp().ReadDataSet(DBConfig.ConnectionSettings, CommandType.Text, sql, p);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ConvertHelper<T>.ConvertToModel(ds.Tables[0]);
            }
            else
            {
                return default(T);
            }
        }
        /// <summary>
        /// 抽出一条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ColVar">值</param>
        /// <param name="primaryKey">检索列名</param>
        /// <param name="where"></param>
        /// <returns></returns>
        public T Get_SetStringVarCol<T>(string ColVar, string primaryKey, string where) where T : new()
        {
            string whereStr = GetWhere(where);
            T t = new T();
            Type type = typeof(T);
            string sql = "select top 1 " + ConvertHelper<T>.GetCollectionsColumnName() + " from [" + type.Name + "] where [" + primaryKey + "]=@id " + (string.IsNullOrEmpty(whereStr) ? "" : " and " + whereStr);
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@id", ColVar);
            DataSet ds = new DbHelp().ReadDataSet(DBConfig.ConnectionSettings, CommandType.Text, sql, p);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ConvertHelper<T>.ConvertToModel(ds.Tables[0]);
            }
            else
            {
                return default(T);
            }
        }
        /// <summary>
        /// 抽出一条记录
        /// </summary>
        /// <param name="sql">参数化的SQL语句</param>
        /// <param name="SqlParameter">参数</param>
        /// <returns></returns>
        public T Get<T>(string sqlWhere, params DbParameter[] SqlParameter) where T : new()
        {
            Type type = typeof(T);
            string sql = "select top 1 " + ConvertHelper<T>.GetCollectionsColumnName() + " from [" + type.Name + "]";
            if (!string.IsNullOrEmpty(sqlWhere.Trim()))
            {
                sql = sql + " where " + sqlWhere;
            }

            DataSet ds = new DbHelp().ReadDataSet(DBConfig.ConnectionSettings, CommandType.Text, sql, SqlParameter);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ConvertHelper<T>.ConvertToModel(ds.Tables[0]);
            }
            else
            {
                return default(T);
            }
        }

        public T Get<T>(string SQL) where T : new()
        {
            List<T> list = ConvertHelper<T>.ConvertToModelList(new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, SQL));
            if (list != null && list.Count > 0)
            {
                return list[0];
            }
            return default(T);
        }
        #endregion
        #region 统计总数据条数
        /// <summary>
        /// 统计总数据条数，根据主键ID
        /// </summary>
        /// <typeparam name="T">数据库表映射的模型</typeparam>
        /// <param name="primaryKey">主键名称</param>
        /// <returns></returns>
        public int GetCount<T>(string primaryKey) where T : new()
        {
            int Result = 0;
            int.TryParse(new DbHelp().GetScalar(DBConfig.ConnectionSettings, CommandType.Text, "SELECT COUNT(" + primaryKey + ") FROM " + typeof(T).Name).ToString(), out Result);
            return Result;
        }
        #endregion
        #region 统计充值成功后的总钱数 
        /// <summary>
        /// 统计总数据条数，根据主键ID
        /// </summary>
        /// <typeparam name="T">数据库表映射的模型</typeparam>
        /// <param name="primaryKey">主键名称</param>
        /// <returns></returns>
        public int GetCountBySql<T>(string sql) where T : new()
        {
            int Result = 0;
            int.TryParse(new DbHelp().GetScalar(DBConfig.ConnectionSettings, CommandType.Text, sql).ToString(), out Result);
            return Result;
        }
        /// <summary>
        /// 统计指定条件存在数据条数
        /// </summary>
        /// <typeparam name="T">数据库表映射模型</typeparam>
        /// <param name="primaryKey">主键名称</param>
        /// <param name="sqlWhere">查询条件</param>
        /// <returns></returns>
        public int GetCount<T>(string primaryKey, string sqlWhere) where T : new()
        {
            int Result = 0;
            sqlWhere = GetWhere(sqlWhere);
            if (sqlWhere.Trim() == "")
            {
                int.TryParse(new DbHelp().GetScalar(DBConfig.ConnectionSettings, CommandType.Text, "SELECT COUNT(" + primaryKey + ") FROM [" + typeof(T).Name + "]").ToString(), out Result);
            }
            else
            {
                int.TryParse(new DbHelp().GetScalar(DBConfig.ConnectionSettings, CommandType.Text, "SELECT COUNT(" + primaryKey + ") FROM [" + typeof(T).Name + "] WHERE " + sqlWhere).ToString(), out Result);
            }
            return Result;
        }
        public int GetCount<T>(string primaryKey, string sqlWhere, params DbParameter[] SqlParameter) where T : new()
        {
            int Result = 0;
            sqlWhere = GetWhere(sqlWhere);
            if (sqlWhere.Trim() == "")
            {
                int.TryParse(new DbHelp().GetScalar(DBConfig.ConnectionSettings, CommandType.Text, "SELECT COUNT(" + primaryKey + ") FROM [" + typeof(T).Name + "]", SqlParameter).ToString(), out Result);
            }
            else
            {
                int.TryParse(new DbHelp().GetScalar(DBConfig.ConnectionSettings, CommandType.Text, "SELECT COUNT(" + primaryKey + ") FROM [" + typeof(T).Name + "] WHERE " + sqlWhere, SqlParameter).ToString(), out Result);
            }
            return Result;
        }
        #endregion

        #region 抽出数据表数据
        /// <summary>
        /// 抽取模型对应表数据
        /// </summary>
        /// <param name="orderBy">排序不需要 order by</param>
        /// <returns></returns>
        public List<T> GetListOrderBy<T>(string orderBy) where T : new()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select " + ConvertHelper<T>.GetCollectionsColumnName() + " from [");
            sql.Append(typeof(T).Name);
            sql.Append("]");
            if (!string.IsNullOrEmpty(orderBy.Trim()))
            {
                sql.Append(" order by " + orderBy);
            }
            return ConvertHelper<T>.ConvertToModelList(new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, sql.ToString()));
        }
        /// <summary>
        /// 抽取模型对应表数据
        /// </summary>
        /// <param name="orderBy">排序</param>
        /// <returns></returns>
        public DataTable GetDataTableOrderBy<T>(string orderBy) where T : new()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select " + ConvertHelper<T>.GetCollectionsColumnName() + " from ");
            sql.Append(typeof(T).Name);
            if (!string.IsNullOrEmpty(orderBy.Trim()))
            {
                sql.Append(" order by " + orderBy);
            }
            return new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, sql.ToString());
        }
        /// <summary>
        /// 抽取模型对应表数据
        /// </summary>
        /// <param name="orderBy">排序不需要 order by</param>
        /// <param name="whereStr">条件语句，不需要加where</param>
        /// <returns></returns>
        public List<T> GetListOrderBy<T>(string orderBy, string whereStr) where T : new()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select " + ConvertHelper<T>.GetCollectionsColumnName() + " from ");
            sql.Append(typeof(T).Name);

            whereStr = GetWhere(whereStr);

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql.Append(" where " + whereStr);
            }
            if (!string.IsNullOrEmpty(orderBy.Trim()))
            {
                sql.Append(" order by " + orderBy);
            }
            return ConvertHelper<T>.ConvertToModelList(new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, sql.ToString()));
        }

        public List<T> GetListOrderBy<T>(string orderBy, string whereStr, string topStr, string ColumnNmae) where T : new()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select  " + topStr + " " + ColumnNmae + " from ");
            sql.Append(typeof(T).Name);

            whereStr = GetWhere(whereStr);

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql.Append(" where " + whereStr);
            }
            if (!string.IsNullOrEmpty(orderBy.Trim()))
            {
                sql.Append(" order by " + orderBy);
            }
            return ConvertHelper<T>.ConvertToModelList(new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, sql.ToString()));
        }


        /// <summary>
        /// 抽取模型对应表数据
        /// </summary>
        /// <param name="orderBy">排序</param>
        /// <param name="whereStr">条件语句，不需要加where</param>
        /// <returns></returns>
        public DataTable GetDataTableOrderBy<T>(string orderBy, string whereStr) where T : new()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select " + ConvertHelper<T>.GetCollectionsColumnName() + " from [");
            sql.Append(typeof(T).Name);
            sql.Append("]");
            whereStr = GetWhere(whereStr);
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql.Append(" where " + whereStr);
            }
            if (!string.IsNullOrEmpty(orderBy.Trim()))
            {
                sql.Append(" order by " + orderBy);
            }
            return new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, sql.ToString());
        }

        /// <summary>
        /// 抽取模型对应表数据
        /// </summary>
        /// <returns></returns>
        public List<T> GetList<T>(string SQL) where T : new()
        {
            return ConvertHelper<T>.ConvertToModelList(new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, SQL));
        }

        /// <summary>
        /// 抽取模型对应表数据
        /// </summary>
        /// <returns></returns>
        public List<T> GetAll<T>() where T : new()
        {
            Type type = typeof(T);
            string SQL = "select * from [" + type.Name + "]";
            return ConvertHelper<T>.ConvertToModelList(new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, SQL));
        }
        /// <summary>
        /// 抽取模型对应表数据
        /// </summary>
        /// <returns></returns>
        public List<T> GetAll<T>(string SQL) where T : new()
        {
            return ConvertHelper<T>.ConvertToModelList(new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, SQL));
        }
        /// <summary>
        /// 抽取模型对应表数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable<T>(string SQL) where T : new()
        {
            return new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, SQL);
        }
        /// <summary>
        /// 抽取模型对应表数据
        /// </summary>
        /// <returns></returns>
        public List<T> GetList<T>(string SQL, params DbParameter[] SqlParameter) where T : new()
        {
            if (SqlParameter == null)
            {
                return ConvertHelper<T>.ConvertToModelList(new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, SQL, SqlParameter));
            }
            else
            {
                return ConvertHelper<T>.ConvertToModelList(new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, SQL));
            }
        }
        /// <summary>
        /// 抽取模型对应表数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable<T>(string SQL, params DbParameter[] SqlParameter) where T : new()
        {
            if (SqlParameter!= null)
            {
                return new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, SQL, SqlParameter);
            }
            else
            {
                return new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, SQL);
            }
        }
        /// <summary>
        /// 抽取模型对应表数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetOneTable(string SQL)
        {
            return new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, SQL);
        }
        #endregion

        #region 抽出数据表数据-含分页
        /// <summary>
        /// 抽取模型对应表数据
        /// </summary>
        /// <param name="PageIndex">当前页码</param>
        /// <param name="PageSize">每页数据量</param>
        /// <param name="identityId">自增编号</param>
        /// <param name="orderBy">排序,不需加order by</param>
        /// <returns></returns>
        public List<T> GetPage_List<T>(int PageIndex, int PageSize, string identityId, string orderBy) where T : new()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT TOP ");
            sql.Append(PageSize);
            sql.Append(" ");
            sql.Append(ConvertHelper<T>.GetCollectionsColumnName());
            sql.Append(" FROM [");
            sql.Append(typeof(T).Name);
            sql.Append("]");
            if (PageSize * PageIndex > 0)
            {
                sql.Append(" WHERE ");
                sql.Append(identityId);
                sql.Append(" NOT IN(");
                //
                sql.Append("SELECT ");
                sql.Append(" TOP ");
                sql.Append(PageSize * PageIndex);

                sql.Append(" ");
                sql.Append(identityId);
                sql.Append(" FROM [");
                sql.Append(typeof(T).Name);
                sql.Append("]");
                if (!string.IsNullOrEmpty(orderBy.Trim()))
                {
                    sql.Append(" order by " + orderBy);
                }               
                sql.Append(")");
            }
            if (!string.IsNullOrEmpty(orderBy.Trim()))
            {
                sql.Append(" order by " + orderBy);
            }
            return ConvertHelper<T>.ConvertToModelList(new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, sql.ToString()));
        }
        /// <summary>
        /// 抽取模型对应表数据
        /// </summary>
        /// <param name="PageIndex">当前页码</param>
        /// <param name="PageSize">每页数据量</param>
        /// <param name="identityId">自增编号</param>
        /// <param name="orderBy">排序</param>
        public DataTable GetPage_DataTable<T>(int PageIndex, int PageSize, string identityId, string orderBy) where T : new()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT TOP ");
            sql.Append(PageSize);
            sql.Append(" ");
            sql.Append(ConvertHelper<T>.GetCollectionsColumnName());
            sql.Append(" FROM ");
            sql.Append(typeof(T).Name);
            if (PageSize * PageIndex > 0)
            {
                sql.Append(" WHERE ");
                sql.Append(identityId);
                sql.Append(" NOT IN(");
                
                sql.Append("SELECT ");
                sql.Append(" TOP ");
                sql.Append(PageSize * PageIndex);
                sql.Append(" ");
                sql.Append(identityId);
                sql.Append(" FROM ");
                sql.Append(typeof(T).Name);
                if (!string.IsNullOrEmpty(orderBy.Trim()))
                {
                    sql.Append(" order by " + orderBy);
                }                
                sql.Append(")");
            }
            if (!string.IsNullOrEmpty(orderBy.Trim()))
            {
                sql.Append(" order by " + orderBy);
            }
            return new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, sql.ToString());
        }

        /// <summary>
        /// 抽取模型对应表数据
        /// </summary>
        /// <param name="PageIndex">当前页码</param>
        /// <param name="PageSize">每页数据量</param>
        /// <param name="identityId">自增编号</param>
        /// <returns></returns>
        public List<T> GetPage_List<T>(int PageIndex, int PageSize, string identityId, string orderBy, string sqlWhere, params DbParameter[] SqlParameter) where T : new()
        {
            sqlWhere = GetWhere(sqlWhere);
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            if (PageSize > 0)
            {
                sql.Append("  TOP " + PageSize);
            }
            sql.Append(" ");
            sql.Append(ConvertHelper<T>.GetCollectionsColumnName());
            sql.Append(" FROM [");
            sql.Append(typeof(T).Name);
            sql.Append("]");
            if (PageIndex * PageSize > 0)
            {
                sql.Append(" WHERE ");
                sql.Append(identityId);
                sql.Append(" NOT IN(");
                
                sql.Append("SELECT TOP ");
                sql.Append(PageSize * PageIndex);
                sql.Append(" ");
                sql.Append(identityId);
                sql.Append(" FROM ");
                sql.Append(typeof(T).Name);
                //条件
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    sql.Append(" WHERE ");
                    sql.Append(sqlWhere);
                }
                //排序
                if (!string.IsNullOrEmpty(orderBy.Trim()))
                {
                    sql.Append(" order by " + orderBy);
                }
                //
                sql.Append(") ");
                //条件
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    sql.Append(" and " + sqlWhere);
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    sql.Append(" WHERE ");
                    sql.Append(sqlWhere);
                }
            }
            if (!string.IsNullOrEmpty(orderBy.Trim()))
            {
                sql.Append(" order by " + orderBy);
            }
            return ConvertHelper<T>.ConvertToModelList(new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, sql.ToString(), SqlParameter));
        }
        public List<T> GetPage_List<T>(int PageIndex, int PageSize, string identityId, string orderBy, string sqlWhere, string showColNames, params DbParameter[] SqlParameter) where T : new()
        {
            sqlWhere = GetWhere(sqlWhere);
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            if (PageSize > 0)
            {
                sql.Append("  TOP " + PageSize);
            }
            sql.Append(" ");
            sql.Append(showColNames);
            sql.Append(" FROM [");
            sql.Append(typeof(T).Name);
            sql.Append("]");
            if (PageIndex * PageSize > 0)
            {
                sql.Append(" WHERE ");
                sql.Append(identityId);
                sql.Append(" NOT IN(");
               
                sql.Append("SELECT TOP ");
                sql.Append(PageSize * PageIndex);
                sql.Append(" ");
                sql.Append(identityId);
                sql.Append(" FROM ");
                sql.Append(typeof(T).Name);
                //条件
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    sql.Append(" WHERE ");
                    sql.Append(sqlWhere);
                }
                //排序
                if (!string.IsNullOrEmpty(orderBy.Trim()))
                {
                    sql.Append(" order by " + orderBy);
                }
               
                sql.Append(") ");
                //条件
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    sql.Append(" and " + sqlWhere);
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    sql.Append(" WHERE ");
                    sql.Append(sqlWhere);
                }

            }
            if (!string.IsNullOrEmpty(orderBy.Trim()))
            {
                sql.Append(" order by " + orderBy);
            }
            return ConvertHelper<T>.ConvertToModelList(new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, sql.ToString(), SqlParameter));
        }       
        public List<T> GetPage_List_from<T>(int PageIndex, int PageSize, string identityId, string orderBy, string sqlWhere,string fromTable) where T : new()
        {
            sqlWhere = GetWhere(sqlWhere);
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            if (PageSize > 0)
            {
                sql.Append("  TOP " + PageSize);
            }
            sql.Append(" ");
            sql.Append(ConvertHelper<T>.GetCollectionsColumnName());
            sql.Append(" FROM ");
            sql.Append(fromTable);

            if (PageIndex * PageSize > 0)
            {
                sql.Append(" WHERE ");
                sql.Append(identityId);
                sql.Append(" NOT IN(");
                
                sql.Append("SELECT TOP ");
                sql.Append(PageSize * PageIndex);
                sql.Append(" ");
                sql.Append(identityId);
                sql.Append(" FROM ");
                sql.Append(fromTable);
                //条件
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    sql.Append(" WHERE ");
                    sql.Append(sqlWhere);
                }
                //排序
                if (!string.IsNullOrEmpty(orderBy.Trim()))
                {
                    sql.Append(" order by " + orderBy);
                }
                //
                sql.Append(") ");
                //条件
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    sql.Append(" and " + sqlWhere);
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    sql.Append(" WHERE ");
                    sql.Append(sqlWhere);
                }

            }
            if (!string.IsNullOrEmpty(orderBy.Trim()))
            {
                sql.Append(" order by " + orderBy);
            }           
            return ConvertHelper<T>.ConvertToModelList(new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, sql.ToString()));
        }
        public List<T> GetPage_List_from<T>(int PageIndex, int PageSize, string identityId, string orderBy, string sqlWhere,string showColNames, string fromTable) where T : new()
        {
            sqlWhere = GetWhere(sqlWhere);
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            if (PageSize > 0)
            {
                sql.Append("  TOP " + PageSize);
            }
            sql.Append(" ");
            sql.Append(showColNames);
            sql.Append(" FROM ");
            sql.Append(fromTable);

            if (PageIndex * PageSize > 0)
            {
                sql.Append(" WHERE ");
                sql.Append(identityId);
                sql.Append(" NOT IN(");
               
                sql.Append("SELECT TOP ");
                sql.Append(PageSize * PageIndex);
                sql.Append(" ");
                sql.Append(identityId);
                sql.Append(" FROM ");
                sql.Append(fromTable);
                //条件
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    sql.Append(" WHERE ");
                    sql.Append(sqlWhere);
                }
                //排序
                if (!string.IsNullOrEmpty(orderBy.Trim()))
                {
                    sql.Append(" order by " + orderBy);
                }
                //
                sql.Append(") ");
                //条件
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    sql.Append(" and " + sqlWhere);
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    sql.Append(" WHERE ");
                    sql.Append(sqlWhere);
                }
            }
            if (!string.IsNullOrEmpty(orderBy.Trim()))
            {
                sql.Append(" order by " + orderBy);
            }

            return ConvertHelper<T>.ConvertToModelList(new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, sql.ToString()));
        }
        /// <summary>
        /// 抽取模型对应表数据
        /// </summary>
        /// <param name="PageIndex">当前页码</param>
        /// <param name="PageSize">每页数据量</param>
        /// <param name="identityId">自增编号</param>
        /// <returns></returns>
        public DataTable GetPage_DataTable<T>(int PageIndex, int PageSize, string identityId, string orderBy, string sqlWhere, params DbParameter[] SqlParameter) where T : new()
        {
            sqlWhere = GetWhere(sqlWhere);
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT TOP ");
            sql.Append(PageSize);
            sql.Append(" ");
            sql.Append(ConvertHelper<T>.GetCollectionsColumnName());
            sql.Append(" FROM ");
            sql.Append(typeof(T).Name);

            if (PageIndex * PageSize > 0)
            {
                sql.Append(" WHERE ");
                sql.Append(identityId);
                sql.Append(" NOT IN(");

                sql.Append("SELECT TOP ");
                sql.Append(PageSize * PageIndex);
                sql.Append(" ");
                sql.Append(identityId);
                sql.Append(" FROM ");
                sql.Append(typeof(T).Name);
                //条件
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    sql.Append(" WHERE ");
                    sql.Append(sqlWhere);
                }
                //排序
                if (!string.IsNullOrEmpty(orderBy.Trim()))
                {
                    sql.Append(" order by " + orderBy);
                }
                //
                sql.Append(") ");
                //条件
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    sql.Append(" and " + sqlWhere);
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    sql.Append(" WHERE ");
                    sql.Append(sqlWhere);
                }
            }
            if (!string.IsNullOrEmpty(orderBy.Trim()))
            {
                sql.Append(" order by " + orderBy);
            }
            return new DbHelp().ReadDataTable(DBConfig.ConnectionSettings, CommandType.Text, sql.ToString(), SqlParameter);
        }

        #endregion

        #region 处理Where条件
        /// <summary>
        /// 处理Where条件
        /// </summary>
        /// <param name="sqlWhere">where 条件</param>
        /// <returns></returns>
        private string GetWhere(string sqlWhere)
        {
            if (string.IsNullOrEmpty(sqlWhere))
            {
                return "";
            }
            StringBuilder sql = new StringBuilder();
            sqlWhere = sqlWhere.TrimEnd().TrimStart();
            if (sqlWhere.Length > 3 && sqlWhere.Substring(0, 3).ToLower() != "or " && sqlWhere.Substring(0, 4).ToLower() != "and ")
            {
                sql.Append(sqlWhere);
                sql.Append(" ");
            }
            else
            {
                if (sqlWhere.Length > 3 && sqlWhere.Substring(0, 3).ToLower() == "or ")
                {
                    sql.Append(sqlWhere.Remove(0, 2));
                    sql.Append(" ");
                }
                if (sqlWhere.Length > 3 && sqlWhere.Substring(0, 4).ToLower() == "and ")
                {
                    sql.Append(sqlWhere.Remove(0, 3));
                    sql.Append(" ");
                }
            }
            return sql.ToString();
        }

        #endregion

        private static string strTablePkMax = "TInsert_Max";
        /// <summary>
        /// 生成主键(数据库现有记录的最大主键加1),若出现错误则抛出异常
        /// </summary>
        /// <param name="strTableName">要生成主键的表+字段的名称</param>
        /// <returns>生成的主键(数据库现有记录的最大主键加1),若出现错误则抛出异常</returns>
        public static long GetMaxId(string tableAndColName)
        {
            try
            {
                string strTableName = tableAndColName;
                if (string.IsNullOrEmpty(strTableName))
                {
                    throw new System.Exception("TableName is null or empty!");
                }
                strTableName = strTableName.Trim();
                if (string.IsNullOrEmpty(strTableName))
                {
                    throw new System.Exception("TableName is null or empty!");
                }
                long longPkMax = 0;
                string sql = "select top 1 IMax from [" + strTablePkMax + "] where [VC_TabName]=@VC_TabName";
                SqlParameter[] p = new SqlParameter[1];
                p[0] = new SqlParameter("@VC_TabName", strTableName);
                DataSet ds = DbHelp.ExecuteDataset(DBConfig.ConnectionSettings, CommandType.Text, sql, p);
                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    longPkMax = long.Parse(ds.Tables[0].Rows[0][0].ToString());
                }
                else
                {
                    p = new SqlParameter[1];
                    p[0] = new SqlParameter("@VC_TabName", strTableName);
                    sql = "insert into [" + strTablePkMax + "](VC_TabName,IMax)values(@VC_TabName,0);";
                    if (DbHelp.ExecuteNonQuery(DBConfig.ConnectionSettings, CommandType.Text, sql, p) > 0)
                    {
                        longPkMax = 0;
                    }
                    else
                    {
                        throw new System.Exception("insert wrong!");
                    }
                }
                longPkMax = longPkMax + 1;
                sql = "update [" + strTablePkMax + "] set [IMax]=" + longPkMax.ToString() + " WHERE VC_TabName=@VC_TabName";
                p = new SqlParameter[1];
                p[0] = new SqlParameter("@VC_TabName", strTableName);
                if (DbHelp.ExecuteNonQuery(DBConfig.ConnectionSettings, CommandType.Text, sql, p) > 0)
                {
                    return longPkMax;
                }
                else
                {
                    throw new System.Exception("update wrong!");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}